drop table jms_tmp.dm_tms_road_waybill_linepart_sum_hf;
create external table jms_tmp.dm_tms_road_waybill_linepart_sum_hf
(
    shipment_no                       string comment '任务编号'
    ,shipment_name                    string comment '任务名称'
    ,vehicleline_code                 string comment '参考车线编码'
    ,vehicleline_name                 string comment '参考车线名称'
    ,linepart_name                    string comment '线路段名称'
    ,mileage                          string comment '系统里程'
    ,linepart_mileage                 decimal(15,2) comment '线路段里程'
    ,business_attribute               string comment '任务类型 1正班2加班'
    ,has_out                          string comment '是否兜底线路'
    ,strategy_line                    string comment '是否战略线路'
    ,operation_model                  string comment '运行模式 1单边2对开3往返'
    ,plate_number                     string comment '车牌号'
    ,vehicletype_id                   string comment '车型ID'
    ,vehicletype_name                 string comment '车型'
    ,shipno_planned_departure_time    timestamp comment '任务单规划发车时间'
    ,shipno_actual_departure_time     timestamp comment '任务单实际发车时间'
    ,shipno_planned_arrival_time      timestamp comment '任务单规划到车时间'
    ,shipno_actual_arrival_time       timestamp comment '任务单实际到车时间'
    ,linepart_planned_departure_time  timestamp comment '线路段规划发车时间'
    ,linepart_actual_departure_time   timestamp comment '线路段实际发车时间'
    ,linepart_planned_arrival_time    timestamp comment '线路段规划到车时间'
    ,linepart_actual_arrival_time     timestamp comment '线路段实际到车时间'
    ,start_site_code                  string comment '出发站点编号'
    ,start_site_name                  string comment '出发站点名称'
    ,start_site_sort_num              string comment '出发站点顺序编号'
    ,end_site_code                    string comment '目的站点编码'
    ,end_site_name                    string comment '目的站点名称'
    ,end_site_sort_num                string comment '目的站点顺序编号'
    ,start_code                       string comment '始发中心编号'
    ,start_name                       string comment '始发中心名称'
    ,end_code                         string comment '目的中心编号'
    ,end_name                         string comment '目的中心名称'
    ,linepart_count                   bigint comment '线路段装载票数'
    ,min_start_site_sort_num          bigint comment '最小发件站点顺序编号'
    ,min_end_site_sort_num            bigint comment '最小下一站顺序编号'
    ,vehiclevolume                    decimal(15,5) comment '核载体积m3'
    ,vehicleweight                    decimal(15,5) comment '核载重量(单位：吨)'
    ,vehiclecount                     decimal(15,5) comment '核载票数'
    ,averagevolume                    decimal(15,5) comment '平均单票体积'
    ,start_province_id                string comment '始发省份ID'
    ,start_province_name              string comment '始发省份名称'
    ,start_region_id                  string comment '始发大区ID'
    ,start_region_name                string comment '始发大区名称'
    ,start_agent_code                 string comment '始发代理区ID'
    ,start_agent_name                 string comment '始发代理区名称'
    ,end_province_id                  string comment '目的省份ID'
    ,end_province_name                string comment '目的省份名称'
    ,end_region_id                    string comment '目的大区ID'
    ,end_region_name                  string comment '目的大区名称'
    ,end_agent_code                   string comment '目的代理区ID'
    ,end_agent_name                   string comment '目的代理区名称'
    ,start_site_province_id           string comment '始发站点省份ID'
    ,start_site_province_name         string comment '始发站点省份名称'
    ,start_site_region_id             string comment '始发站点大区ID'
    ,start_site_region_name           string comment '始发站点大区名称'
    ,start_site_agent_code            string comment '始发站点代理区ID'
    ,start_site_agent_name            string comment '始发站点代理区名称'
    ,end_site_province_id             string comment '目的站点省份ID'
    ,end_site_province_name           string comment '目的站点省份名称'
    ,end_site_region_id               string comment '目的站点大区ID'
    ,end_site_region_name             string comment '目的站点大区名称'
    ,end_site_agent_code              string comment '目的站点代理区ID'
    ,end_site_agent_name              string comment '目的站点代理区名称'
)
comment '临时表-线路段装载票数'
stored as parquet
location '/dw/hive/jms_tmp.db/external/dm_tms_road_waybill_linepart_sum_hf'
;



drop table jms_dm.dm_tms_shipment_line_load_rate_ht;
create external table jms_dm.dm_tms_shipment_line_load_rate_ht
(
     shipment_no                         string comment '任务单号'
    ,shipment_name                       string comment '线路名称'
    ,date_time                           date   comment '运行日期'
    ,vehicleline_code                    string comment '线路编码'
    ,linepart_name                       string comment '线路段'
    ,mileage                             string comment '任务单公里数'
    ,linepart_mileage                    decimal(15,2) comment '线路段公里数'
    ,business_attribute                  string comment '任务类型 1正班2加班'
    ,has_out                             string comment '是否兜底1是2否'
    ,strategy_line                       string comment '否战略线路1是2否'
    ,operation_model                     string comment '运行模式 1单边2对开3往返'
    ,plate_number                        string comment '车牌号'
    ,vehicletype_id                      string comment '车型ID'
    ,vehicletype_name                    string comment '车型名称'
    ,shipno_planned_departure_time       timestamp comment '任务单规划发车时间'
    ,shipno_actual_departure_time        timestamp comment '任务单实际发车时间'
    ,shipno_planned_arrival_time         timestamp comment '任务单规划到车时间'
    ,shipno_actual_arrival_time          timestamp comment '任务单实际到车时间'
    ,linepart_planned_departure_time     timestamp comment '线路段规划发车时间'
    ,linepart_actual_departure_time      timestamp comment '线路段实际发车时间'
    ,linepart_planned_arrival_time       timestamp comment '线路段规划到车时间'
    ,linepart_actual_arrival_time        timestamp comment '线路段实际到车时间'
    ,start_site_code                     string comment '始发站点编号'
    ,start_site_name                     string comment '始发站点名称'
    ,start_site_sort_num                 string comment '始发站点顺序编号'
    ,end_site_code                       string comment '站点发件下一站编码'
    ,end_site_name                       string comment '站点发件下一站名称'
    ,end_site_sort_num                   string comment '目的站点顺序编号'
    ,start_code                          string comment '始发中心编号'
    ,start_name                          string comment '始发中心名称'
    ,end_code                            string comment '目的中心编号'
    ,end_name                            string comment '目的中心名称'
    ,linepart_count                      bigint comment '装载票数'
    ,plan_count                          bigint comment '核载票数'
    ,linepart_rate                       decimal(10,2) comment '线路段装载率'
    ,line_count                          bigint comment '线路装载票数'
    ,first_site_plan_linepart_count      bigint comment '首站核载票数'
    ,line_rate                           decimal(10,2) comment '线路装载率'
    ,start_province_id                   string comment '始发省份ID'
    ,start_province_name                 string comment '始发省份名称'
    ,start_region_id                     string comment '始发大区ID'
    ,start_region_name                   string comment '始发大区名称'
    ,start_agent_code                    string comment '始发代理区ID'
    ,start_agent_name                    string comment '始发代理区名称'
    ,end_province_id                     string comment '目的省份ID'
    ,end_province_name                   string comment '目的省份名称'
    ,end_region_id                       string comment '目的大区ID'
    ,end_region_name                     string comment '目的大区名称'
    ,end_agent_code                      string comment '目的代理区ID'
    ,end_agent_name                      string comment '目的代理区名称'
    ,start_site_province_id              string comment '始发站点省份ID'
    ,start_site_province_name            string comment '始发站点省份名称'
    ,start_site_region_id                string comment '始发站点大区ID'
    ,start_site_region_name              string comment '始发站点大区名称'
    ,start_site_agent_code               string comment '始发站点代理区ID'
    ,start_site_agent_name               string comment '始发站点代理区名称'
    ,end_site_province_id                string comment '目的站点省份ID'
    ,end_site_province_name              string comment '目的站点省份名称'
    ,end_site_region_id                  string comment '目的站点大区ID'
    ,end_site_region_name                string comment '目的站点大区名称'
    ,end_site_agent_code                 string comment '目的站点代理区ID'
    ,end_site_agent_name                 string comment '目的站点代理区名称'
    ,update_time                         string comment '更新时间'
)
comment '干线线路装载率'
partitioned by (dt string comment '规划发车日期分区')
stored as parquet
location '/dw/hive/jms_dm.db/external/dm_tms_shipment_line_load_rate_ht'
;


drop table `jms_dm`.`dm_tms_shipment_line_load_rate_ht`;
CREATE TABLE `jms_dm`.`dm_tms_shipment_line_load_rate_ht` (
  `shipment_no`                     varchar(255)  COMMENT "线路编号",
  `shipment_name`                   varchar(255)  COMMENT "任务单号",
  `date_time`                       date  COMMENT "运行日期",
  `vehicleline_code`                varchar(255)  COMMENT "线路编码",
  `linepart_name`                   varchar(255)  COMMENT "线路段",
  `mileage`                         varchar(255)  COMMENT "系统里程",
  `linepart_mileage`                decimal(12,2)        comment '线路段公里数',
  `business_attribute`              varchar(255)  COMMENT "任务类型",
  `has_out`                         varchar(255)  COMMENT "是否兜底1是2否",
  `strategy_line`                   varchar(255)  COMMENT "否战略线路1是2否",
  `operation_model`                 varchar(255)  COMMENT "运行模式",
  `plate_number`                    varchar(255)  COMMENT "车牌号",
  `vehicletype_id`                  varchar(255)  COMMENT "车型ID",
  `vehicletype_name`                varchar(255)  COMMENT "车型名称",
  `shipno_planned_departure_time`   datetime      COMMENT "任务单规划发车时间",
  `shipno_actual_departure_time`    datetime      COMMENT "任务单实际发车时间",
  `shipno_planned_arrival_time`     datetime      COMMENT "任务单规划到车时间",
  `shipno_actual_arrival_time`      datetime      COMMENT "任务单实际到车时间",
  `linepart_planned_departure_time` datetime      comment "线路段规划发车时间",
  `linepart_actual_departure_time`  datetime      comment "线路段实际发车时间",
  `linepart_planned_arrival_time`   datetime      comment "线路段规划到车时间",
  `linepart_actual_arrival_time`    datetime      comment "线路段实际到车时间",
  `start_site_code`                 varchar(255)  COMMENT "始发站点编号",
  `start_site_name`                 varchar(255)  COMMENT "始发站点名称",
  `start_site_sort_num`             varchar(255)  COMMENT "始发站点顺序编号",
  `end_site_code`                   varchar(255)  COMMENT "站点发件下一站编码",
  `end_site_name`                   varchar(255)  COMMENT "站点发件下一站名称",
  `end_site_sort_num`               varchar(255)  COMMENT "目的站点顺序编号",
  `start_code`                      varchar(255)  COMMENT "始发中心编号",
  `start_name`                      varchar(255)  COMMENT "始发中心名称",
  `end_code`                        varchar(255)  COMMENT "目的中心编号",
  `end_name`                        varchar(255)  COMMENT "目的中心名称",
  `linepart_count`                  bigint(20)    COMMENT "装载票数",
  `plan_count`                      bigint(20)    COMMENT "核载票数",
  `linepart_rate`                   decimal(10, 2) COMMENT "线路段装载率",
  `line_count`                      bigint(20)     COMMENT "线路装载票数",
  `first_site_plan_linepart_count`  bigint(20)     COMMENT "首站核载票数",
  `line_rate`                       decimal(10, 2) COMMENT "线路装载率",
  `start_province_id`               varchar(255)  COMMENT "始发省份ID",
  `start_province_name`             varchar(255)  COMMENT "始发省份名称",
  `start_region_id`                 varchar(255)  COMMENT "始发大区ID",
  `start_region_name`               varchar(255)  COMMENT "始发大区名称",
  `start_agent_code`                varchar(255)  COMMENT "始发代理区ID",
  `start_agent_name`                varchar(255)  COMMENT "始发代理区名称",
  `end_province_id`                 varchar(255)  COMMENT "目的省份ID",
  `end_province_name`               varchar(255)  COMMENT "目的省份名称",
  `end_region_id`                   varchar(255)  COMMENT "目的大区ID",
  `end_region_name`                 varchar(255)  COMMENT "目的大区名称",
  `end_agent_code`                  varchar(255)  COMMENT "目的代理区ID",
  `end_agent_name`                  varchar(255)  COMMENT "目的代理区名称",
  `start_site_province_id`          varchar(255)  COMMENT "始发站点省份ID",
  `start_site_province_name`        varchar(255)  COMMENT "始发站点省份名称",
  `start_site_region_id`            varchar(255)  COMMENT "始发站点大区ID",
  `start_site_region_name`          varchar(255)  COMMENT "始发站点大区名称",
  `start_site_agent_code`           varchar(255)  COMMENT "始发站点代理区ID",
  `start_site_agent_name`           varchar(255)  COMMENT "始发站点代理区名称",
  `end_site_province_id`            varchar(255)  COMMENT "目的站点省份ID",
  `end_site_province_name`          varchar(255)  COMMENT "目的站点省份名称",
  `end_site_region_id`              varchar(255)  COMMENT "目的站点大区ID",
  `end_site_region_name`            varchar(255)  COMMENT "目的站点大区名称",
  `end_site_agent_code`             varchar(255)  COMMENT "目的站点代理区ID",
  `end_site_agent_name`             varchar(255)  COMMENT "目的站点代理区名称",
  `update_time`                     varchar(255)  comment "更新时间"
) ENGINE=OLAP
DUPLICATE KEY(`shipment_no`)
COMMENT "干线线路装载率"
PARTITION BY RANGE(`date_time`)(
    START ("2023-12-01") END ("2024-01-15") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(`shipment_no`) BUCKETS 1
PROPERTIES (
    "replication_num" = "3",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.time_zone" = "Asia/Shanghai",
    "dynamic_partition.start" = "-365",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "1",
    "in_memory" = "false",
    "storage_format" = "V2",
    "enable_persistent_index" = "false"
);